<?php
set_include_path('system'.PATH_SEPARATOR.'templates');

require_once('Template.php');
require_once('RedirectBrowserException.php');
require_once('DatabaseUtility.php');

$tmpl = new Template();

$org = $_GET['org'];
$ref = $_GET['ref'];
$type = $_GET['type'];

openDatabase();

if ($type=="pro") {
    redirect(getProteinReport($org,$ref));
} else if($type=="site") {
    redirect(getSiteReport($org,$ref));
}else if($type=="nrpep") {
    redirect(getNrPepReport($org,$ref));
}else if($type=="spec") {
    redirect(getSpectrumReport($org,$ref));
}

$tmpl->organismSelect = getOrganismSelect();
$tmpl->dataSourceSelect = getDataSourceSelect();
$tmpl->current = 'download';
$tmpl->pageContent = $tmpl->build('download.tmpl');

print $tmpl->build('page.tmpl');

closeDatabase();

function redirect($file) {
	//print '<html><head>';
	//print '<meta http-equiv="REFRESH" content="0;url='.$file.'">';
	//print '</head></html>';
	
	if (file_exists($file)) {
		header('Content-Description: File Transfer');
		header('Content-Type: application/octet-stream');
		header('Content-Disposition: attachment; filename='.basename($file));
		header('Content-Transfer-Encoding: binary');
		header('Expires: 0');
		header('Cache-Control: must-revalidate');
		header('Pragma: public');
		header('Content-Length: ' . filesize($file));
		ob_clean();
		flush();
		readfile($file);
		exit;
	}
}

function getProteinReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphoprotein-report";

    $query = "SELECT id,annotation, sequence,latin ".
             "FROM protein, organism ".
             "WHERE protein.organism=organism.label";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND protein.organism='$org'";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND EXISTS (SELECT * FROM proteinDataSourceRelation WHERE proteinDataSourceRelation.protein=protein.id AND proteinDataSourceRelation.datasource='$ref')";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $zp = gzopen($file, 'w9') or die('Failed to open: '.$file);
    gzwrite($zp, "P3DB Protein ID\tXref\tAnnotation\tSequence\tOrganism\tPubMed\n") or die('Failed to write: '.$file);

	$result = runQuery($query);
	foreach($result as $row) {
        $id = $row['id'];
        $xref = implode(';',getXref($id));
        $pubmed = formatDataSources(getDataSourceProtein($id));
        $ann = $row['annotation'];
        $seq = $row['sequence'];
        $org = $row['latin'];
        gzwrite($zp, "$id\t$xref\t$ann\t$seq\t$org\t$pubmed\n") or die('Failed to write: '.$file);
    }

    gzclose($zp) or die('Failed to close: '.$file);

    return $file;
}

function getSiteReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphosite-report";
    $query = "SELECT id,protein,locationInProtein FROM site WHERE ptm='p'";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=site.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND EXISTS (SELECT * FROM siteDataSourceRelation WHERE siteDataSourceRelation.site=site.id AND siteDataSourceRelation.datasource='$ref')";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $zp = gzopen($file, 'w9') or die('Failed to open: '.$file);
    gzwrite($zp, "P3DB Protein ID\tXref\tP3DB Site ID\tLocation\tPubMed\n");

    $result = runQuery($query);
	foreach($result as $row) {
        $id = $row['id'];
        $pubmed = formatDataSources(getDataSourceSite($id));
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $loc = $row['locationInProtein']+1;
        gzwrite($zp, "$protein\t$xref\t$id\t$loc\t$pubmed\n");
    }

    gzclose($zp);

    return $file;
}

function getNrPepReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-nr-phosphopeptide-report";
    $query = "SELECT DISTINCT phosphoSequence,protein,location FROM peptide WHERE 1";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND '$ref'=(SELECT dataSource FROM mass WHERE mass.id=peptide.mass)";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $zp = gzopen($file, 'w9') or die('Failed to open: '.$file);
    gzwrite($zp, "P3DB Protein ID\tXref\tPhosphopeptide\tLocation\n");

    $result = runQuery($query);
	foreach($result as $row) {
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $loc = $row['location']+1;
        $nrseq = $row['phosphoSequence'];
        gzwrite($zp, "$protein\t$xref\t$nrseq\t$loc\n");
    }

    gzclose($zp);

    return $file;
}

function getSpectrumReport($org,$ref) {
    $file = "data/p3db-".getP3DBVersion()."-phosphopeptide-report";
    $query = "SELECT peptide.id,phosphoSequence,protein,location,mass.id as msid,file,scan,reference,pubmed FROM peptide,mass,dataSource WHERE peptide.mass=mass.id AND mass.dataSource=dataSource.id";
    if (!empty($org)) {
        $file .= '_'.appendixOrg($org);
        $query .= " AND '$org'=(SELECT organism FROM protein WHERE protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $file .= '_'.appendixRef($ref);
        $query .= " AND '$ref'=dataSource";
    }

    $file .= ".gz";

    if (file_exists($file))
        return $file;

    $zp = gzopen($file, 'w9') or die('Failed to open: '.$file);
    gzwrite($zp, "P3DB Protein ID\tXref\tP3DB Peptide ID\tSpectrum\tPhosphopeptide\tLocation\tPubMed or Reference");
    $params = getRecordsFromATable("peptideParameterType","1");
    foreach ($params as $param) {
        gzwrite($zp, "\t".$param['name']);
    }
    gzwrite($zp, "\n");

    $pepparams = getPeptideParamInMap($org, $ref);

    $result = runQuery($query);
	foreach($result as $row) {
        $protein = $row['protein'];
        $xref = implode(';',getXref($protein));
        $id = $row['id'];
        $loc = $row['location']+1;
        $nrseq = $row['phosphoSequence'];
        $reference = $row['pubmed'];
        if (empty($reference))
            $reference = $row['reference'];
        if (isset($row['scan']))
            $scan = $row['file'].'.'.$row['scan'];
        else
            $scan = $row['msid'];
        gzwrite($zp, "$protein\t$xref\t$id\t$scan\t$nrseq\t$loc\t$reference");
        foreach ($params as $param) {
            gzwrite($zp, "\t");
            gzwrite($zp, $pepparams[$id][$param['id']]);
        }
        gzwrite($zp, "\n");
    }

    gzclose($zp);

    return $file;
}

function getPeptideParamInMap($org,$ref) {
    $query = "SELECT * FROM peptideParameter WHERE 1";
    if (!empty($org)) {
        $query .= " AND '$org'=(SELECT organism FROM peptide,protein WHERE peptideParameter.peptide=peptide.id AND protein.id=peptide.protein)";
    }
    if (!empty($ref)) {
        $query .= " AND '$ref'=(SELECT dataSource FROM peptide,mass WHERE peptideParameter.peptide=peptide.id AND mass.id=peptide.mass)";
    }

    $ret = array();
    $result = runQuery($query);
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $ret[$row['peptide']][$row['parameter']] = $row['value'];
    }
    return $ret;
}

function formatDataSources($dataSources) {
    $pubmed = array();
    foreach ($dataSources as $ds) {
        $pubmed[] = $ds['pubmed'];
    }
    return implode(';', $pubmed);
}

function appendixOrg($org) {
    if (empty($org)) return "";
	
    $query = "SELECT latin FROM organism WHERE label='$org'";
    $result = runQuery($query);
    $row = $result[0];
    $latin = $row['latin'];
    return str_replace(' ', '-', $latin);
}

function appendixRef($ref) {
    if (empty($ref))
        return "";
    $query = "SELECT pubmed FROM dataSource WHERE id='$ref'";
    $result = runQuery($query);
    $row = $result[0];
    $pubmed = $row['pubmed'];
    if (!empty($pubmed))
        return "pubmed-$pubmed";
    return $ref;
}
?>